/*
	Copyright (C) 2017 Shanghai Huizhao e-Bidding Services Co., Ltd.
	All rights reserved.

	Author: wanfy
	Version: 1.0
	Created Time: 2017年6月12日 下午4:18:26
	
	Revision History:
	Version          Date               Author			Comments
	1.0         	2017年6月12日下午4:18:26		wanfy			Create file
=========================================================================
 */

package com.example.demo.common.mybatis.config;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wanfy
 * 
 */
public class OracleDao implements DatabaseDao {

	/*
	 * keyType 字段类型（ C CHECK类型,P 主键,U 唯一性约束,R 外键,V视图中CHECK,O视图中只读）
	 */

	@Override
	public List<Property> queryProperties(Connection conn, String tableName, String databaseName) throws SQLException {
		tableName=tableName.toUpperCase();
		List<Property> list = new ArrayList<>();

		String sql = "SELECT s.COLUMN_NAME columnName, s.DATA_TYPE dataType,"
				+ "s.DATA_PRECISION numericPrecision, s.DATA_SCALE numericScale ,r.CONSTRAINT_TYPE constraintType"
				+ " from (select * from USER_TAB_COLUMNS where TABLE_NAME='"+tableName+"' ) s left join ( "
				+ "select uc. constraint_type,ucc.column_name" + " FROM user_cons_columns ucc, user_constraints uc"
				+ " WHERE ucc.TABLE_NAME = '"+tableName+"' "
				+ "and ucc.constraint_name = uc.constraint_name AND uc.status = 'ENABLED' "
				+ "AND uc.constraint_type = 'P'" + ") r on s.COLUMN_NAME=r.column_name";
//		QueryRunner qr = new QueryRunner();
//		List<Property> list = (List<Property>) qr.query(conn, sql, new BeanListHandler(Property.class), new String[] {
//				tableName, tableName });
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql.toString());
			rs=ps.executeQuery();
			if(rs.next()){

                Property property = new Property();
                property.setColumnName(rs.getString("columnName"));
                property.setDataType(rs.getString("dataType"));
				property.setNumericScale(rs.getInt("numericScale"));
				property.setConstraintType(rs.getString("constraintType"));
				property.setNumericPrecision(rs.getInt("numericPrecision"));
                list.add(property);

            }
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			ps.close();
			rs.close();
			conn.close();
		}
		return list;
	}
}
